----------------------------------------------------------------

--유병현씨
create table con(
conno number(5) constraint con_conno_pk primary key,
conid varchar2(20) not null,
conname varchar2(15) not null,
conpw varchar2(40) not null,
conpno varchar2(20) not null,
conaddr varchar2(60) not null,
conregdate date default sysdate,
conflag varchar(15) not NULL
);

--이현욱씨

create table menu(
mno number(5)  constraint menu_mno_pk primary key,
mname varchar2(20) not null,
mprice NUMBER(10) not null,
regdate date default sysdate
);

--김은지씨
create table orders(
ono NUMBER(5) constraint orders_ono_pk primary key,
conno number(5),
mno NUMBER(5),
oqt NUMBER(5) not null,
odate date default sysdate,
bill VARCHAR2(10) not null,
constraint orders_conno_fk foreign key(conno) references con(conno),
constraint orders_mno_fk foreign key(mno) references menu(mno)
);

--박정훈
create table review(
reno NUMBER(5) constraint review_reno_pk primary key,
conno NUMBER(5),
mno NUMBER(5), 
reregdate DATE DEFAULT SYSDATE,
regrd NUMBER(1),
recomment varchar2(1000)
constraint review_conno_fk foreign key(conno) references con(conno),
constraint review_mno_fk foreign key(mno) references menu(mno)
);


--안상천씨

메뉴번호 (mno)'fk' 
레시피 번호(rno) pk
요리 메인 사진(r_main_photo)
요리 주재료 (r_ingredients1) ex)돼지고기,밀가루...
요리 부재료 (r_ingredients2)
요리 재료설명 (r_ingredients3)
요리 시간(제조시간,분량,칼로리) (r_time)
요리 분량(제조시간,분량,칼로리) (r_quantity)
요리 칼로리(제조시간,분량,칼로리) (r_kcal)


create table recipe(
rno number(5) constraint recipe_rno_pk primary key,
mno NUMBER(5) references menu(mno),
r_main_photo VARCHAR2(100)not null,
r_ingredients1 VARCHAR2(1000)not null,
r_ingredients2 VARCHAR2(1000)not null,
r_ingredients3 VARCHAR2(1000)not null,
r_time VARCHAR2(100)not null,
r_quantity VARCHAR2(100)not null,
r_kcal VARCHAR2(100)not null
);

요리과정(recipe_process)
요리과정번호(rpno)
요리과정사진(rp_photo)
요리과정설명(rp_desc)
요리과정순서(rp_order_no)

create table recipe_process(
rpno number(5) constraint recipe_processrno_rpno_pk primary key,
rno NUMBER(5),
rp_photo VARCHAR2(100)not null,
rp_desc VARCHAR2(1000)not null,
rp_order_no number(5)not null,
constraint recipe_process_rno_fk foreign key(rno) references recipe(rno)
) ;



--일련번호(시퀀스) 생성
create sequence rno nocache;

select * from seq;

--데이터 입력
insert into review(rno,usno,mno,rgrd)
values(rno.nextval,usno.nextval,mno.nextval,7);

insert into professor(pfno,pfname,fields,skills)
values(pfno.nextval,'태희','컴퓨터공학','DB,보안');


-------------------------------------------------------------
